package com.overoid.mynote.data;


import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.overoid.mynote.Constants;

public class DBHelper {
	
	public static final String DB_NAME = "memo";
	public static final int DB_VERSION = 2;
	
	private static final String CLASSNAME = DBHelper.class.getSimpleName();
	private static final String TB_NAME_MEMO_CLASS = "memo_class";
	private static final String TB_NAME_MEMO = "memo";
	private static final String TB_NAME_MEMO_MEDIA = "memo_media";
	
	private SQLiteDatabase db;
	private final DBOpenHelper dbOpenHelper;
	
	///
	/// inner class
	///
	private static class DBOpenHelper extends SQLiteOpenHelper {
		
		private static final String MEMO_CLASS_CREATE = " CREATE TABLE memo ( "
			+ " memo_id INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL, "
			+ " memo_icon INTEGER  NULL, "
			+ " memo_contents TEXT  NULL, "
			+ " memo_reg_dt TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL, "
			+ " memo_udt_dt TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL, "
			+ " memo_class_id INTEGER  NULL, "
			+ " memo_title TEXT  NULL "
			+ " );";
		
		private static final String MEMO_CREATE = " CREATE TABLE memo_class ( "
			+ " memo_class_id INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT, "
			+ " memo_class_name TEXT  NULL, "
			+ " memo_class_bg_image TEXT  NULL, "
			+ " memo_class_reg_dt TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL, "
			+ " memo_class_udt_dt TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL "
			+ " ); ";

		private static final String MEMO_MEDIA_CREATE = " CREATE TABLE memo_media ( "
			+ " memo_id INTEGER  NULL, "
			+ " media_id INTEGER  NULL, "
			+ " media_type_id INTEGER  NULL, "
			+ " media_uri TEXT  NULL, "
			+ " media_reg_dt TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL, "
			+ " media_udt_dt TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL, "
			+ " PRIMARY KEY (memo_id,media_id) "
			+ " ); "; 
		
		private static final String MEMO_INDEX1_CREATE = "CREATE UNIQUE INDEX memo_uix "
			+ " ON memo ( memo_id  ASC);";

		private static final String MEMO_INDEX2_CREATE = "CREATE INDEX memo_ix2 "
			+ " ON memo ( memo_icon, memo_id );";
		
		private static final String MEMO_INDEX3_CREATE = "CREATE INDEX memo_ix3 "
			+ " ON memo ( memo_class_id, memo_id );";
		
		private static final String MEMO_CLASS_INDEX_CREATE = " CREATE INDEX memo_class_uix "
			+ " ON memo_class ( memo_class_id );";
		
		private static final String MEMO_MEDIA_INDEX_CREATE = " CREATE INDEX memo_media_uix "
			+ " ON memo_media ( memo_id, media_id );";

		public DBOpenHelper(final Context context) {
            super(context, DBHelper.DB_NAME, null, DBHelper.DB_VERSION);
        }
		
		@Override
        public void onCreate(SQLiteDatabase db) {
            try {
            	Log.v(Constants.LOGTAG, DBHelper.CLASSNAME + " - table creation ");
                db.execSQL(MEMO_CLASS_CREATE);
                db.execSQL(MEMO_CREATE);
                db.execSQL(MEMO_MEDIA_CREATE);
                
                Log.v(Constants.LOGTAG, DBHelper.CLASSNAME + " - index creation ");
                db.execSQL(MEMO_INDEX1_CREATE);
                db.execSQL(MEMO_INDEX2_CREATE);
                db.execSQL(MEMO_INDEX3_CREATE);
                db.execSQL(MEMO_CLASS_INDEX_CREATE);
                db.execSQL(MEMO_MEDIA_INDEX_CREATE);
                
                // 초기 테스트 데이터 - 향후 삭제 해야 함.
                initDataInsert(db);
                
            } catch (SQLException e) {
                Log.e(Constants.LOGTAG, DBHelper.CLASSNAME, e);
            }
        }

        @Override
        public void onOpen(final SQLiteDatabase db) {
            super.onOpen(db);
        }

        @Override
        public void onUpgrade(final SQLiteDatabase db, final int oldVersion, final int newVersion) {
            Log.v(Constants.LOGTAG, DBHelper.CLASSNAME + " - onUpgrade ");
            db.execSQL("DROP TABLE IF EXISTS memo");
            db.execSQL("DROP TABLE IF EXISTS memo_class");
            db.execSQL("DROP TABLE IF EXISTS memo_media");
            onCreate(db);

        }
		
        
        private void initDataInsert(SQLiteDatabase db) {
        	Log.v(Constants.LOGTAG, DBHelper.CLASSNAME + " - initDataInsert() ");
            db.execSQL("insert into memo_class(memo_class_name) values('업무자료');");
            db.execSQL("insert into memo_class(memo_class_name, memo_class_bg_image) " +
            			"values('주요 메모 사항','ico_folder_orange'); ");
            db.execSQL("insert into memo_class(memo_class_name, memo_class_bg_image) " +
            			"values('최근 이슈 항목','ico_folder_blue'); ");
            
            db.execSQL("insert into memo(memo_icon, memo_contents, memo_class_id, memo_title) values(1,'메모 첫번째 자료임.',1,'메모 첫번째 제목');");
            db.execSQL("insert into memo(memo_icon, memo_contents, memo_class_id, memo_title) values(1,'메모 두번째 자료임.',2,'메모 두번째 제목');");
        }
        
	}
	
	// memo Table TO Object
	public static class Memo {
		public long memoId;
		public int memoIcon;
		public String memoContents;
		public String memoRegistDate;
		public String memoUpdateDate;
		public long memoClassId;
		public String memoTitle;
		public String memoClassName;
		public long imageCount;
		public long movieCount;
		public long soundCount; 
		
		public Memo() {}

		
		
		public Memo(long memoId, int memoIcon, String memoContents,
				String memoRegistDate, String memoUpdateDate, long memoClassId,
				String memoTitle, String memoClassName, long imageCount, long movieCount,
				long soundCount) {
			this.memoId = memoId;
			this.memoIcon = memoIcon;
			this.memoContents = memoContents;
			this.memoRegistDate = memoRegistDate;
			this.memoUpdateDate = memoUpdateDate;
			this.memoClassId = memoClassId;
			this.memoTitle = memoTitle;
			this.memoClassName = memoClassName;
			this.imageCount = imageCount;
			this.movieCount = movieCount;
			this.soundCount = soundCount;
		}

		public String toString() {
			return this.memoContents.substring(1,30) + " ...";
		}
		
	}
	
	// memo_class table TO class
	public static class MemoClass {
		public long memoClasId;
		public String memoClassName;
		public String memoClassBackgroudImage;
		public String memoClassRegistDate;
		public String memoClassUpdateDate;
		public long memoCount;
		
		MemoClass() {}

		public MemoClass(long memoClasId, String memoClassName,
				String memoClassBackgroudImage, String memoClassRegistDate,
				String memoClassUpdateDate, long memoCount) {
			this.memoClasId = memoClasId;
			this.memoClassName = memoClassName;
			this.memoClassBackgroudImage = memoClassBackgroudImage;
			this.memoClassRegistDate = memoClassRegistDate;
			this.memoClassUpdateDate = memoClassUpdateDate;
			this.memoCount = memoCount;
		}
		
		public String toString() {
			return this.memoClassName;
		}
		
	}
	
	// memo_media table TO class
	public static class MemoMedia {
		public long memoId;
		public long mediaId;
		public int mediaTypeId;
		public String mediaUri;
		public String mediaRegistDate;
		public String mediaUpdateDate;
		
		MemoMedia() {}

		public MemoMedia(long memoId, long mediaId, int mediaTypeId,
				String mediaUri, String mediaRegistDate, String mediaUpdateDate) {
			this.memoId = memoId;
			this.mediaId = mediaId;
			this.mediaTypeId = mediaTypeId;
			this.mediaUri = mediaUri;
			this.mediaRegistDate = mediaRegistDate;
			this.mediaUpdateDate = mediaUpdateDate;
		}
		
		public String toString() {
			return this.mediaUri;
		}
		
	}
	
	///
	/// inner classes end
	
	public DBHelper(final Context context) {
        this.dbOpenHelper = new DBOpenHelper(context);
        establishDb();
    }

    private void establishDb() {
        if (this.db == null) {
            this.db = this.dbOpenHelper.getWritableDatabase();
        }
    }

    public void cleanup() {
        if (this.db != null) {
            this.db.close();
            this.db = null;
        }
    }
    
    public List<Memo> getMemoList() {
    	Cursor c = null;
    	ArrayList<Memo> ret = new ArrayList<Memo>();
    	String q = " select m.memo_id "
    		       + "     ,m.memo_icon "
    		       + "     ,m.memo_title "
    		       + "     ,m.memo_contents "
    		       + "     ,m.memo_class_id "
    		       + "     ,c.memo_class_name "
    		       + "     ,strftime('%Y-%m-%d %H:%M:%S',m.memo_reg_dt) memo_reg_dt "
    		       + "     ,ifnull(i.image_count,0) image_count "
    		       + "     ,ifnull(i.movie_count,0) movie_count "
    		       + "     ,ifnull(i.sound_count,0) sound_count "
    		       + " from memo m left outer join memo_class c "
    		       + "      on m.memo_class_id = c.memo_class_id "
    		       + "      left outer join (select memo_id "
    		       + "                             ,case when media_type_id = 1 then media_count else 0 end as image_count "
    		       + "                             ,case when media_type_id = 2 then media_count else 0 end as movie_count "
    		       + "                             ,case when media_type_id = 3 then media_count else 0 end as sound_count "
    		       + "                          from ( "
    		       + "                                select memo_id "
    		       + "                                      ,media_type_id "
    		       + "                                      ,count(media_id) media_count "
    		       + "                                  from memo_media "
    		       + "                                 group by memo_id, media_type_id) "
    		       + "                         group by memo_id) i "
    		       + "       on m.memo_id = i.memo_id" 
    		       + " order by m.memo_reg_dt desc;";
    	
    	try {
    		c = this.db.rawQuery(q, null);
    		int numRows = c.getCount();
    		c.moveToFirst();
    		
    		for(int i=0; i < numRows; i++) {
    			Memo m = new Memo();
    			m.memoId = c.getLong(0);
    			m.memoIcon = c.getInt(1);
    			m.memoTitle = c.getString(2);
    			m.memoContents = c.getString(3);
    			m.memoClassId = c.getLong(4);
    			m.memoClassName = c.getString(5);
    			m.memoRegistDate = c.getString(6);
    			m.imageCount = c.getLong(7);
    			m.movieCount = c.getLong(8);
    			m.soundCount = c.getLong(9);
    			
    			ret.add(m);
    			c.moveToNext();
    		}
    		
    	} catch (SQLException e) {
    		Log.e(Constants.LOGTAG, DBHelper.CLASSNAME + " - List<Memo> getMemoList()", e );
    	} finally {
    		if (c != null && !c.isClosed()) {
    			c.close();
    		}
    	}
    	
    	return ret;
    }
	
    public List<MemoClass> getMemoClassList() {
    	Cursor c = null;
    	ArrayList<MemoClass> ret = new ArrayList<MemoClass>();
    	String q =  "select ifnull(m.memo_class_id,0) memo_class_id " +
			    	"      ,ifnull(c.memo_class_name,'') memo_class_name " +
			    	"      ,ifnull(c.memo_class_bg_image,'') memo_class_bg_image " +
			    	"      ,count(m.memo_id) memo_count " +
			    	"  from memo m left outer join memo_class c " +
			    	"    on m.memo_class_id = c.memo_class_id " +
			    	" group by m.memo_class_id " +
			    	"         ,c.memo_class_name " ;
			    	
    	try {
    		c = this.db.rawQuery(q, null);
    		int numRows = c.getCount();
    		c.moveToFirst();
    		
    		for(int i=0; i < numRows; i++) {
    			MemoClass mc = new MemoClass();
    			mc.memoClasId = c.getLong(0);
    			mc.memoClassName = c.getString(1);
    			mc.memoClassBackgroudImage = c.getString(2);
    			mc.memoCount = c.getLong(3);
    			ret.add(mc);
    			c.moveToNext();
    		}
    		
    	} catch (SQLException e) {
    		Log.e(Constants.LOGTAG, DBHelper.CLASSNAME + " - List<Memo> getMemoList()", e );
    	} finally {
    		if (c != null && !c.isClosed()) {
    			c.close();
    		}
    	}
    	
    	return ret;
    }
    
    public void insertMemoClass(final MemoClass memoClass) {
    	ContentValues values = new ContentValues();
    	values.put("memo_class_name", memoClass.memoClassName);
    	values.put("memo_class_bg_image", memoClass.memoClassBackgroudImage);
    	this.db.insert(DBHelper.TB_NAME_MEMO_CLASS, null, values);
    }
    
    public void updateMemoClass(final MemoClass memoClass) {
    	ContentValues values = new ContentValues();
    	values.put("memo_class_name", memoClass.memoClassName);
    	values.put("memo_class_bg_image", memoClass.memoClassBackgroudImage);
    	String timeStamp = new Timestamp(Calendar.getInstance().getTimeInMillis()).toString();
    	values.put("memo_udt_dt", timeStamp);
    	this.db.update(DBHelper.TB_NAME_MEMO_CLASS, values, "memo_class_id=" + memoClass.memoClasId, null);
    }
    
    public void deleteMemoClass(final long memoClassId) {
    	this.db.delete(DBHelper.TB_NAME_MEMO_CLASS, "memo_class_id=" + memoClassId, null);
    }
	
}
